<?php
/**
 * Created by PhpStorm.
 * User: 郑合达
 * Date: 2018/12/11
 * Time: 17:22
 */

namespace app\admin\controller;

use app\Model\UsersModel;
use app\Model\OrderModel;
use app\Model\OrderGoodsModel;
class Report extends Common
{

    private $page = 20;
    /**
     * @var
     * $begin   开始时间
     * $end     结束时间
     * 查询时间范围处理
     */
    public $begin;
    public $end;
    public function _initialize(){
        $this->getConAndAct('data');
        parent::_initialize();
        $timegap = input('start_time');
        if($timegap){
            $gap = explode(' - ', $timegap);
            $begin = $gap[0];
            $end = $gap[1];
        }else{
            $lastweek = date('Y-m-d',strtotime("-1 month"));//30天前
            $begin = input('begin',$lastweek);
            $end =  input('end',date('Y-m-d'));
        }
        $this->begin = strtotime($begin);
        $this->end = strtotime($end)+86399;
        $this->assign('start_time',date('Y-m-d',$this->begin).' - '.date('Y-m-d',$this->end));
    }


    public function index(){
        $now = strtotime(date('Y-m-d'));
        //今日销售总额
        $today['today_amount'] = Db('tp_order')->where("add_time>$now AND (pay_status=1 or pay_code='cod') and order_status in(1,2,4)")->sum('order_amount');//今日销售总额
     //今日订单数
        $today['today_order'] = Db('tp_order')->where("add_time>$now and (pay_status=1 or pay_code='cod')")->count();//今日订单数
        //今日取消订单
        $today['cancel_order'] = Db('tp_order')->where("add_time>$now AND order_status=3")->count();//今日取消订单
        if($today['today_order']==0){
            $today['sign'] = 0;
        }else{
            $today['sign'] = round($today['today_amount']/$today['today_order'],2);//人均客单价
        }

        $this->assign('today',$today);

        $sql = "SELECT COUNT(*) as tnum,sum(order_amount) as amount, FROM_UNIXTIME(add_time,'%Y-%m-%d') as gap from  tp_order ";
        $sql .= " where add_time>$this->begin and add_time<$this->end AND (pay_status=1 or pay_code='cod') and order_status in(1,2,4) group by gap ";
        $res = Db()->query($sql);//订单数,交易额
//          $where['pay_status']=1;
//          $where['pay_code']='cod';
//        $res = OrderModel::field('count(*) as tnum,sum(order_amount) as amount,FROM_UNIXTIME(add_time,\'%Y-%m-%d\') as gap')
//            ->whereTime('add_time', 'between', [$this->begin, $this->end])
//            ->whereOr($where)
//            ->where('order_status','in',[1,2,4])
//            ->group('gap')
//            ->select();
//        dump($this->begin);
//        dump($this->end);
//dump($res);die();
           $tnum=0;
           $tamount=0;
        foreach ($res as $val){
            $arr[$val['gap']] = $val['tnum'];
            $brr[$val['gap']] = $val['amount'];
            $tnum += $val['tnum'];
            $tamount += $val['amount'];
        }

        for($i=$this->begin;$i<=$this->end;$i=$i+24*3600){
            $tmp_num = empty($arr[date('Y-m-d',$i)]) ? 0 : $arr[date('Y-m-d',$i)];
            $tmp_amount = empty($brr[date('Y-m-d',$i)]) ? 0 : $brr[date('Y-m-d',$i)];
            $tmp_sign = empty($tmp_num) ? 0 : round($tmp_amount/$tmp_num,2);
            $order_arr[] = $tmp_num;
            $amount_arr[] = $tmp_amount;
            $sign_arr[] = $tmp_sign;
            $date = date('Y-m-d',$i);
            $list[] = array('day'=>$date,'order_num'=>$tmp_num,'amount'=>$tmp_amount,'sign'=>$tmp_sign,'end'=>date('Y-m-d',$i+24*60*60));
            $day[] = $date;
        }

        $this->assign('list',$list);
        $result = array('order'=>$order_arr,'amount'=>$amount_arr,'sign'=>$sign_arr,'time'=>$day);
        $this->assign('result',json_encode($result));
       return view('index');
    }

    /**
     * @return \think\response\View
     * 返回会员统计数据到页面
     */
 public function user(){
     $today = strtotime(date('Y-m-d'));
     $month = strtotime(date('Y-m-01'));

     $user['today'] = UsersModel::where("reg_time>$today")->count('user_id');//今日新增会员
     $user['month'] = UsersModel::where("reg_time>$month")->count('user_id');//本月新增会员


     $user['total'] = UsersModel::count('user_id');//会员总数

     $user['user_money'] = UsersModel::sum('user_money');//会员余额总额

     $res = OrderModel::field('user_id')->select();

     $user['hasorder'] = count($res);  //有单会员数

     $this->assign('user',$user);

     $sql = "SELECT COUNT(*) as num,FROM_UNIXTIME(reg_time,'%Y-%m-%d') as gap from tp_users where reg_time>$this->begin and reg_time<$this->end group by gap";
     $new = Db()->query($sql);//新增会员趋势
     foreach ($new as $val){
         $arr[$val['gap']] = $val['num'];
     }
      //整理数据成{data[0,0,0,0],time[0,0,0,0]}
     for($i=$this->begin;$i<=$this->end;$i=$i+24*3600){
         $brr[] = empty($arr[date('Y-m-d',$i)]) ? 0 : $arr[date('Y-m-d',$i)];
         $day[] = date('Y-m-d',$i);
     }
     $result = array('data'=>$brr,'time'=>$day);
     $this->assign('result',json_encode($result));
     return view('user');
 }

    /**
     * @return \think\response\View
     * 返回数据到财务统计页面
     */
 public function finance(){
     $where['or.pay_status'] = 1;
     $where['or.shipping_status'] = 1;
     $where['og.is_send'] = 1;

     $res = OrderModel::alias('or')->field('sum(or.shipping_price) as shipping_amount,sum(or.coupon_price) as coupon_amount,FROM_UNIXTIME(or.add_time,\'%Y-%m-%d\') as gap')
         ->field('sum(og.goods_num*og.member_goods_price) as goods_amount,sum(og.goods_num*og.cost_price) as cost_price')
         ->join('tp_order_goods og','or.order_id=og.order_id')
         ->whereTime('or.add_time', 'between', [$this->begin, $this->end])
         ->where($where)
         ->group('gap')
         ->order('or.add_time')
         ->select();
     foreach ($res as $val){
         $arr[$val['gap']] = $val['goods_amount'];
         $brr[$val['gap']] = $val['cost_price'];
         $crr[$val['gap']] = $val['shipping_amount'];
         $drr[$val['gap']] = $val['coupon_amount'];
     }

     for($i=$this->begin;$i<=$this->end;$i=$i+24*3600){
         $date = $day[] = date('Y-m-d',$i);
         $tmp_goods_amount = empty($arr[$date]) ? 0 : $arr[$date];
         $tmp_cost_amount = empty($brr[$date]) ? 0 : $brr[$date];
         $tmp_shipping_amount = empty($crr[$date]) ? 0 : $crr[$date];
         $tmp_coupon_amount = empty($drr[$date]) ? 0 : $drr[$date];

         $goods_arr[] = $tmp_goods_amount;
         $cost_arr[] = $tmp_cost_amount;
         $shipping_arr[] = $tmp_shipping_amount;
         $coupon_arr[] = $tmp_coupon_amount;
         $list[] = array('day'=>$date,'goods_amount'=>$tmp_goods_amount,'cost_amount'=>$tmp_cost_amount,
             'shipping_amount'=>$tmp_shipping_amount,'coupon_amount'=>$tmp_coupon_amount,'end'=>date('Y-m-d',$i+24*60*60));
     }
     $this->assign('list',$list);
     $result = array('goods_arr'=>$goods_arr,'cost_arr'=>$cost_arr,'shipping_arr'=>$shipping_arr,'coupon_arr'=>$coupon_arr,'time'=>$day);
     $this->assign('result',json_encode($result));
return view('finance');
 }

    /**
     * @return \think\response\View
     * 返回数据到销售排行页面
     */
    public function saleTop(){
        $res['list'] = OrderGoodsModel::field('goods_name,goods_sn,sum(goods_num) as sale_num,sum(goods_num*goods_price) as sale_amount')
            ->where('is_send','=',1)
            ->group('goods_id')
            ->order('sale_amount DESC')
            ->cache(true,3600)
            ->paginate($this->page,false,['query'=>request()->param()]);

        return view('saleTop',$res);
    }

    /**
     * @return \think\response\View
     * 返回数据到会员排行页面
     */
    public function userTop()
    {
        $mobile = input('mobile');
        $email = input('email');
        if ($mobile) {
            $where['u.mobile'] = $mobile;
        }
        if ($email) {
            $where['u.email'] = $email;
        }
        $where['or.pay_status'] = 1;

        $res['list'] = OrderModel::alias('or')->field('count(or.order_id) as order_num,sum(or.order_amount) as amount,or.user_id')
            ->field('u.mobile,u.email,u.nickname')
            ->join('tp_users u', 'or.user_id = u.user_id')
            ->whereTime('or.add_time', 'between', [$this->begin, $this->end])
            ->where($where)
            ->group('or.user_id')
            ->order('amount DESC')
            ->paginate($this->page,false,['query'=>request()->param()]);

         return view('userTop',$res);
    }

    /***
     * @return \think\response\View
     * 返回数据到销售明细页面
     */
    public function saleList(){
     /*跳转地址所传的时间*/
        $cat_id = input('cat_id',0);
        $brand_id = input('brand_id',0);

        $where=[];
        if($cat_id>0){
            $where['g.cat_id'] = $cat_id;
            $this->assign('cat_id',$cat_id);
        }
        if($brand_id>0){
            $where['g.brand_id'] = $brand_id;
            $this->assign('brand_id',$brand_id);
        }

        $res['list'] = OrderGoodsModel::alias('og')->field('og.*')
            ->field('or.order_sn,or.shipping_name,or.pay_name,or.add_time')
            ->join('tp_order or','og.order_id = or.order_id')
            ->join('tp_goods g','og.goods_id = g.goods_id')
            ->whereTime('add_time', 'between', [$this->begin,$this->end])
            ->where($where)
            ->order('or.add_time desc')
            ->paginate($this->page,false,['query'=>request()->param()]);
        $res['categoryList'] =  getSortCategory();//根据字母（A-Z）排序次级分类---商品分类
        $res['brandList']=getSortBrands();//根据字母（A-Z）排序次级分类---品牌

      return view('saleList',$res);
    }
}